package com.augurit.gzcgwsp.webservice.service.impl;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.StringWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Random;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;

import com.augurit.gzcgwsp.commonex.util.lang.DateUtilsEX;
import com.augurit.gzcgwsp.commonex.util.lang.StringUtilsEX;
import com.augurit.gzcgwsp.webservice.dao.ExChangeDataDao;
import com.augurit.gzcgwsp.webservice.service.IKingdomDataForRqsjService;
import com.augurit.gzcgwsp.wfex.common.service.IWfServiceEX;

@Service
@Transactional
public class KingdomDataForRqsjServiceImpl implements IKingdomDataForRqsjService{
	
	private static final Logger log=Logger.getLogger(KingdomDataForRqsjServiceImpl.class);
	
	@Autowired
	private ExChangeDataDao ExChangeDataDao;
	@Autowired
	private IWfServiceEX wfServiceEX;


	
	/*
	 * 生成经营许可的内容(non-Javadoc)
	 * @see com.augurit.gzcgwsp.webservice.service.IDataService#getDatas(java.lang.String, java.lang.String)
	 */
	
	public String  getRqjyxkDatas(String pageNo, String pageSize,String qymc,String ywId,String qyid) {
		pageNo="1";
		pageSize="1000";
		List list=this.getJyxk(pageNo, pageSize,qymc, ywId, qyid);
		String sql=this.getJyxkSql(qymc, ywId, qyid);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("jyxks");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element jyxk=root.addElement("jyxk");
				this.creatJyxkXml(map, jyxk);
			}
		}
		System.out.print("totalcount = "+totalcount);
		try {
			String filePath = wfServiceEX.getItemNameByItemCodeAndTypeCode("jzXMLPath", "JK_WB_FX2").getItemName();
			String fileName = this.createXMLName("rqqy","", "rqjyxkz", ywId, qyid);
			this.createXml(doc, filePath + fileName);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return this.formatXML(doc,"UTF-8");
		//return doc.asXML();
	}


	
	/*
	 * 企业资质
	 * @see com.augurit.gzcgwsp.webservice.service.IKingdomDataService#getRqQyzzDatas(java.lang.String, java.lang.String)
	 */
	@Override
	public String getRqQyzzDatas(String pageNo, String pageSize,String qymc,String ywId,String qyid) {
		pageNo="1";
		pageSize="1000";
		List list=this.getRqQyzz(pageNo, pageSize, qymc, ywId, qyid);
		String sql=this.getRqQyzzSql( qymc, ywId, qyid);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("rqQyzzs");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element rqQyzz=root.addElement("rqQyzz");
				this.creatRqQyzzXml(map, rqQyzz);
			}
		}
		try {
			String filePath = wfServiceEX.getItemNameByItemCodeAndTypeCode("jzXMLPath", "JK_WB_FX2").getItemName();
			String fileName = this.createXMLName("rqqy", "","rqrsqjazwxqyzz", ywId, qyid);
			this.createXml(doc, filePath + fileName);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return this.formatXML(doc,"UTF-8");
		//return doc.asXML();
	}
	
	/*
	 * 供应站
	 * @see com.augurit.gzcgwsp.webservice.service.IKingdomDataService#getRqQyzzDatas(java.lang.String, java.lang.String)
	 */
	@Override
	public String getGyzDatas(String pageNo, String pageSize,String qymc,String ywId,String qyid) {
		pageNo="1";
		pageSize="1000";
		List<Map> qyList = this.getRqJyxkQy(qymc);
		List<Map> gyzLists = new ArrayList<Map>();
		if(StringUtils.isNotBlank(ywId) && StringUtils.isNotBlank(qyid)){
			gyzLists = this.getGyzBySqdw(qymc,ywId,qyid);
		}else{
			if(qyList != null && qyList.size() > 0){
//				System.out.println("qyList = "+qyList.size());			
				for(int i = 0;i < qyList.size() ; i ++){
					Map qyMap=(Map)qyList.get(i); 
					String sqdw = (String)qyMap.get("sqdw");
//					System.out.println("sqdw = "+sqdw);
//					System.out.println("qyid = "+qyid);
//					System.out.println("");
					List<Map> gyzList = this.getGyzBySqdw(sqdw,null,null);
					if(gyzList != null && gyzList.size() > 0){
						for(int t = 0;t<gyzList.size();t++){
							Map map = gyzList.get(t);
							map.put("sqdw", sqdw);
							gyzLists.add(map);
						}					
					}
	 			}
			}
		}
		
		if(gyzLists != null && gyzLists.size() > 0){
			Document doc = DocumentHelper.createDocument();  
			Element root = doc.addElement("gyzs");
			root.addElement("totalcount").addText(String.valueOf(gyzLists.size()));
			for(int i=0 ;i<gyzLists.size();i++){
				Map map=(Map) gyzLists.get(i);
				Element gyz=root.addElement("gyz");
				this.creatGyzXml(map, gyz);
			}
			try {
				String filePath = wfServiceEX.getItemNameByItemCodeAndTypeCode("jzXMLPath", "JK_WB_FX2").getItemName();
				String fileName = this.createXMLName("rqzd","gyz", "rqjyxkz", ywId, qyid);
				this.createXml(doc, filePath + fileName);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}		
		return "";
		//return doc.asXML();
	}
	
	/*
	 * 接收站
	 * @see com.augurit.gzcgwsp.webservice.service.IKingdomDataService#getRqQyzzDatas(java.lang.String, java.lang.String)
	 */
	@Override
	public String getJszDatas(String pageNo, String pageSize,String qymc,String ywId,String qyid) {
		pageNo="1";
		pageSize="1000";
		List<Map> qyList = this.getRqJyxkQy(qymc);
		List<Map> jszLists = new ArrayList<Map>();
		if(StringUtils.isNotBlank(ywId) && StringUtils.isNotBlank(qyid)){
			jszLists = this.getJszBySqdw(qymc,ywId,qyid);
		}else{
			if(qyList != null && qyList.size() > 0){
//				System.out.println("qyList = "+qyList.size());			
				for(int i = 0;i < qyList.size() ; i ++){
					Map qyMap=(Map)qyList.get(i); 
					String sqdw = (String)qyMap.get("sqdw");
//					System.out.println("sqdw = "+sqdw);
//					System.out.println("");
					List<Map> jszList = this.getJszBySqdw(sqdw,null,null);
					if(jszList != null && jszList.size() > 0){
						for(int t = 0;t<jszList.size();t++){
							Map map = jszList.get(t);
							map.put("sqdw", sqdw);
							jszLists.add(map);
						}					
					}
	 			}
			}
		}
		
		if(jszLists != null && jszLists.size() > 0){ 
			Document doc = DocumentHelper.createDocument();  
			Element root = doc.addElement("gyzs");
			root.addElement("totalcount").addText(String.valueOf(jszLists.size()));
			for(int i=0 ;i<jszLists.size();i++){
				Map map=(Map) jszLists.get(i);
				Element gyz=root.addElement("gyz");
				this.creatJszXml(map, gyz);
			}
			try {
				String filePath = wfServiceEX.getItemNameByItemCodeAndTypeCode("jzXMLPath", "JK_WB_FX2").getItemName();
				String fileName = this.createXMLName("rqzd","jsz", "rqjyxkz", ywId, qyid);
				this.createXml(doc, filePath + fileName);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}		
		return "";
	}
	
	/*
	 * 抢险点
	 * @see com.augurit.gzcgwsp.webservice.service.IKingdomDataService#getRqQyzzDatas(java.lang.String, java.lang.String)
	 */
	@Override
	public String getQxdDatas(String pageNo, String pageSize,String qymc,String acceptNo,String qyid) {
		pageNo="1";
		pageSize="1000";
		List list=this.getQxd(pageNo,pageSize,qymc,acceptNo,qyid);
		String sql=this.getQxdSql( qyid, acceptNo);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("qxds");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element qxd=root.addElement("qxd");
				this.creatQxdXml(map, qxd);
			}
		}
		try {
			String filePath = wfServiceEX.getItemNameByItemCodeAndTypeCode("jzXMLPath", "JK_WB_FX2").getItemName();
			String fileName = this.createXMLName("rqqxd", "","rqjyxkz", acceptNo, qyid);
			this.createXml(doc, filePath + fileName);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return this.formatXML(doc,"UTF-8");
		//return doc.asXML();
	}
	
	/**
	 * 格式化xml
	 * @param document
	 * @param charset
	 * @return
	 */
	public String formatXML(Document document, String charset) { 
    	OutputFormat format = OutputFormat.createPrettyPrint(); 
        format.setEncoding(charset); 
        //format= OutputFormat.createCompactFormat();
        StringWriter sw = new StringWriter(); 
        XMLWriter xw = new XMLWriter(sw, format); 
        try {
        	xw.write(document); 
            xw.flush(); 
            xw.close(); 
        } catch (IOException e) { 
                log.error("格式化XML文档发生异常，请检查！" , e); 
        } 
        return sw.toString(); 
	}

	
	/**
	 * 生成经营许可的xml
	 * @param map
	 * @param element
	 */
	private void creatJyxkXml(Map map,Element element){
//		element.addElement("aoge_id").addText((String)map.get("template_code") + "-" + (String)map.get("id") + "-" + (String)map.get("qyid"));
		element.addElement("aoge_id").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));
		element.addElement("template_code").addText((String)map.get("template_code")==null?"":(String)map.get("template_code"));
		element.addElement("sqdw").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));
		element.addElement("address").addText((String)map.get("address")==null?"":(String)map.get("address"));
		element.addElement("post").addText((String)map.get("post")==null?"":(String)map.get("post"));
		String clsj = "";
		if(StringUtils.isNotBlank((String)map.get("clsj"))){
			clsj = ((String)map.get("clsj")).substring(0, ((String)map.get("clsj")).length()-2);
		}
		element.addElement("clsj").addText(clsj);
		element.addElement("gszcdjh").addText((String)map.get("gszcdjh")==null?"":(String)map.get("gszcdjh"));
		element.addElement("ywlxr").addText((String)map.get("ywlxr")==null?"":(String)map.get("ywlxr"));
		element.addElement("ywlxrtel").addText((String)map.get("ywlxrtel")==null?"":(String)map.get("ywlxrtel"));
		element.addElement("email").addText((String)map.get("email")==null?"":(String)map.get("email"));
		element.addElement("qywz").addText((String)map.get("qywz")==null?"":(String)map.get("qywz"));
		element.addElement("fddbr").addText((String)map.get("fddbr")==null?"":(String)map.get("fddbr"));
		element.addElement("fddbrZw").addText((String)map.get("fddbr_zw")==null?"":(String)map.get("fddbr_zw"));
		element.addElement("fddbrZc").addText((String)map.get("fddbr_zc")==null?"":(String)map.get("fddbr_zc"));
		element.addElement("zyfzr").addText((String)map.get("zyfzr")==null?"":(String)map.get("zyfzr"));
		element.addElement("zyfzrZw").addText((String)map.get("zyfzr_zw")==null?"":(String)map.get("zyfzr_zw"));
		element.addElement("zyfzrZc").addText((String)map.get("zyfzr_zc")==null?"":(String)map.get("zyfzr_zc"));
		element.addElement("aqfzr").addText((String)map.get("aqfzr")==null?"":(String)map.get("aqfzr"));
		element.addElement("aqfzrZw").addText((String)map.get("aqfzr_zw")==null?"":(String)map.get("aqfzr_zw"));
		element.addElement("aqfzrZc").addText((String)map.get("aqfzr_zc")==null?"":(String)map.get("aqfzr_zc"));
		element.addElement("cz").addText((String)map.get("cz")==null?"":(String)map.get("cz"));
		
		element.addElement("jsfzr").addText((String)map.get("jsfzr")==null?"":(String)map.get("jsfzr"));
		element.addElement("jsfzrZw").addText((String)map.get("jsfzr_zw")==null?"":(String)map.get("jsfzr_zw"));
		element.addElement("jsfzrZc").addText((String)map.get("jsfzr_zc")==null?"":(String)map.get("jsfzr_zc"));
		element.addElement("zczj").addText((String)map.get("zczj")==null?"":(String)map.get("zczj")+"万");
		element.addElement("qylx").addText((String)map.get("qylx")==null?"":(String)map.get("qylx"));
		element.addElement("jyfw").addText((String)map.get("jyfw")==null?"":(String)map.get("jyfw"));
		element.addElement("gqqy").addText((String)map.get("gqqy")==null?"":(String)map.get("gqqy"));
		element.addElement("fwdh").addText((String)map.get("fwdh")==null?"":(String)map.get("fwdh"));
		element.addElement("qxdh").addText((String)map.get("qxdh")==null?"":(String)map.get("qxdh"));
		element.addElement("qyjj").addText((String)map.get("qyjj")==null?"":(String)map.get("qyjj"));
		
		String zsbh = (String)map.get("zsbh");
		element.addElement("zsbh").addText(zsbh==null?"":zsbh);//证书编号
		//有效期  拼出来
		String endyear = (String)map.get("endyear");
		String endmonth = (String)map.get("endmonth");
		String endday = (String)map.get("endday");
		String yxq = "";
		if(StringUtils.isNotBlank(endyear) && StringUtils.isNotBlank(endmonth) && StringUtils.isNotBlank(endday)){
			yxq = DateUtilsEX.ChineseYear2Number(endyear) + "-" + DateUtilsEX.ChineseMonth2Number(endmonth) + "-" + DateUtilsEX.ChineseDay2Number(endday) + " 00:00:00";
		}
		element.addElement("yxq").addText(yxq);//有效期
		//发证日期  拼出来
		String startyear = (String)map.get("startyear");
		String startmonth = (String)map.get("startmonth");
		String startday = (String)map.get("startday");
		String fzrq = "";
		if(StringUtils.isNotBlank(startyear) && StringUtils.isNotBlank(startmonth) && StringUtils.isNotBlank(startday)){
			fzrq = DateUtilsEX.ChineseYear2Number(startyear) + "-" + DateUtilsEX.ChineseMonth2Number(startmonth) + "-" + DateUtilsEX.ChineseDay2Number(startday) + " 00:00:00";
		}
		element.addElement("fzrq").addText(fzrq);//发证日期
		if(StringUtils.isNotBlank(zsbh)){
			element.addElement("fzjg").addText((String)map.get("fzjg")==null?"广州市城市管理委员会":(String)map.get("fzjg"));//发证机关
		}else{
			element.addElement("fzjg").addText("");//发证机关
		}
		
		
		element.addElement("xljy_ssys_sl").addText((String)map.get("xljy_ssys_sl")==null?"":(String)map.get("xljy_ssys_sl"));
		element.addElement("xljy_ssys_ms").addText("");
		element.addElement("xljy_bkys_sl").addText((String)map.get("xljy_bkys_sl")==null?"":(String)map.get("xljy_bkys_sl"));
		element.addElement("xljy_bkys_ms").addText("");
		element.addElement("xljy_zkys_sl").addText((String)map.get("xljy_zkys_sl")==null?"":(String)map.get("xljy_zkys_sl"));
		element.addElement("xljy_zkys_ms").addText("");
		element.addElement("xljy_hj_sl").addText((String)map.get("xljy_hj_sl")==null?"":(String)map.get("xljy_hj_sl"));
		element.addElement("xljy_hj_ms").addText("");
		
		element.addElement("zyjsry_gj_sl").addText((String)map.get("zyjsry_gj_sl")==null?"":(String)map.get("zyjsry_gj_sl"));
		element.addElement("zyjsry_gj_ms").addText("");
		element.addElement("zyjsry_zj_sl").addText((String)map.get("zyjsry_zj_sl")==null?"":(String)map.get("zyjsry_zj_sl"));
		element.addElement("zyjsry_zj_ms").addText("");
		element.addElement("zyjsry_cj_sl").addText((String)map.get("zyjsry_cj_sl")==null?"":(String)map.get("zyjsry_cj_sl"));
		element.addElement("zyjsry_cj_ms").addText("");
		element.addElement("zyjsry_hj_sl").addText((String)map.get("zyjsry_hj_sl")==null?"":(String)map.get("zyjsry_hj_sl"));
		element.addElement("zyjsry_hj_ms").addText("");
		
		element.addElement("aqglry_zcaqgcs_sl").addText((String)map.get("aqglry_zcaqgcs_sl")==null?"":(String)map.get("aqglry_zcaqgcs_sl"));
		element.addElement("aqglry_zcaqgcs_ms").addText("");
		element.addElement("aqglry_zcgjaqzr_sl").addText((String)map.get("aqglry_zcgjaqzr_sl")==null?"":(String)map.get("aqglry_zcgjaqzr_sl"));
		element.addElement("aqglry_zcgjaqzr_ms").addText("");
		element.addElement("aqglry_zczjaqzr_sl").addText((String)map.get("aqglry_zczjaqzr_sl")==null?"":(String)map.get("aqglry_zczjaqzr_sl"));
		element.addElement("aqglry_zczjaqzr_ms").addText("");
		element.addElement("aqglry_zccjaqzr_sl").addText((String)map.get("aqglry_zccjaqzr_sl")==null?"":(String)map.get("aqglry_zccjaqzr_sl"));
		element.addElement("aqglry_zccjaqzr_ms").addText("");
		
		element.addElement("cyryzs").addText((String)map.get("cyryzs")==null?"":(String)map.get("cyryzs"));
		
		//以下的是系统里面没有的字段
		element.addElement("comCode").addText("");
		element.addElement("region").addText("");
		element.addElement("abs_x").addText("");
		element.addElement("abs_y").addText("");
		element.addElement("area").addText("");
		element.addElement("community").addText("");
		element.addElement("street").addText("");
		element.addElement("district").addText("");
		element.addElement("fddbr_dh").addText("");
		element.addElement("response_phone").addText("");
		element.addElement("zyfzr_dh").addText("");
		element.addElement("aqfzr_dh").addText("");
		element.addElement("jsfzr_dh").addText("");
		element.addElement("dispatchPhone").addText("");
		element.addElement("service_man_total").addText("");
		element.addElement("repair_man_total").addText("");
		element.addElement("safe_man_num").addText("");
		element.addElement("safe_man_des").addText("");
	}
	
	/**
	 * 生成企业资质的xml
	 * @param map
	 * @param element
	 */
	private void creatRqQyzzXml(Map map,Element element){
//		element.addElement("aoge_id").addText((String)map.get("template_code") + "-" + (String)map.get("id") + "-" + (String)map.get("qyid"));
		element.addElement("aoge_id").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));
		element.addElement("template_code").addText((String)map.get("template_code")==null?"":(String)map.get("template_code"));
		element.addElement("sqdw").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));
		element.addElement("address").addText((String)map.get("address")==null?"":(String)map.get("address"));
		element.addElement("post").addText((String)map.get("post")==null?"":(String)map.get("post"));
		
		String clsj = "";
		if(StringUtils.isNotBlank((String)map.get("clsj"))){
			clsj = ((String)map.get("clsj")).substring(0, ((String)map.get("clsj")).length()-2);
		}
		element.addElement("clsj").addText(clsj);
		element.addElement("gszcdjh").addText((String)map.get("gszcdjh")==null?"":(String)map.get("gszcdjh"));
		element.addElement("ywlxr").addText((String)map.get("ywlxr")==null?"":(String)map.get("ywlxr"));
		element.addElement("ywlxrtel").addText((String)map.get("ywlxrtel")==null?"":(String)map.get("ywlxrtel"));
		element.addElement("email").addText((String)map.get("email")==null?"":(String)map.get("email"));
		element.addElement("qywz").addText((String)map.get("qywz")==null?"":(String)map.get("qywz"));
		element.addElement("fddbr").addText((String)map.get("fddbr")==null?"":(String)map.get("fddbr"));
		element.addElement("fddbrZw").addText((String)map.get("fddbr_zw")==null?"":(String)map.get("fddbr_zw"));
		element.addElement("fddbrZc").addText((String)map.get("fddbr_zc")==null?"":(String)map.get("fddbr_zc"));
		element.addElement("zyfzr").addText((String)map.get("zyfzr")==null?"":(String)map.get("zyfzr"));
		element.addElement("zyfzrZw").addText((String)map.get("zyfzr_zw")==null?"":(String)map.get("zyfzr_zw"));
		element.addElement("zyfzrZc").addText((String)map.get("zyfzr_zc")==null?"":(String)map.get("zyfzr_zc"));
		element.addElement("aqfzr").addText((String)map.get("aqfzr")==null?"":(String)map.get("aqfzr"));
		element.addElement("aqfzrZw").addText((String)map.get("aqfzr_zw")==null?"":(String)map.get("aqfzr_zw"));
		element.addElement("aqfzrZc").addText((String)map.get("aqfzr_zc")==null?"":(String)map.get("aqfzr_zc"));
		element.addElement("cz").addText((String)map.get("cz")==null?"":(String)map.get("cz"));
		
		element.addElement("jsfzr").addText((String)map.get("jsfzr")==null?"":(String)map.get("jsfzr"));
		element.addElement("jsfzrZw").addText((String)map.get("jsfzr_zw")==null?"":(String)map.get("jsfzr_zw"));
		element.addElement("jsfzrZc").addText((String)map.get("jsfzr_zc")==null?"":(String)map.get("jsfzr_zc"));
		element.addElement("zczj").addText((String)map.get("zczj")==null?"":(String)map.get("zczj")+"万");
		element.addElement("qylx").addText((String)map.get("qylx")==null?"":(String)map.get("qylx"));
		element.addElement("jyfw").addText((String)map.get("jyfw")==null?"":(String)map.get("jyfw"));
		element.addElement("gqqy").addText((String)map.get("gqqy")==null?"":(String)map.get("gqqy"));
		element.addElement("fwdh").addText((String)map.get("fwdh")==null?"":(String)map.get("fwdh"));
		element.addElement("qxdh").addText((String)map.get("qxdh")==null?"":(String)map.get("qxdh"));
		element.addElement("qyjj").addText((String)map.get("qyjj")==null?"":(String)map.get("qyjj"));
		element.addElement("area").addText((String)map.get("area")==null?"":(String)map.get("area"));
		element.addElement("rs_wxg").addText((String)map.get("rs_wxg")==null?"":(String)map.get("rs_wxg"));
		
		
		element.addElement("dwsl_jyrq").addText((String)map.get("dwsl_jyrq")==null?"":(String)map.get("dwsl_jyrq"));
		element.addElement("dwsl_gyrq").addText((String)map.get("dwsl_gyrq")==null?"":(String)map.get("dwsl_gyrq"));
		element.addElement("gasOther_num").addText((String)map.get("gasOther_num")==null?"":(String)map.get("gasOther_num"));
		
		String zsbh = (String)map.get("zsbh");
		element.addElement("zsbh").addText(zsbh==null?"":zsbh);//证书编号
		//发证日期  拼出来
		String year = (String)map.get("year");
		String month = (String)map.get("month");
		String day = (String)map.get("day");
		String fzrq = "";
		if(StringUtils.isNotBlank(year) && StringUtils.isNotBlank(month) && StringUtils.isNotBlank(day)){
			fzrq = DateUtilsEX.ChineseYear2Number(year) + "-" + DateUtilsEX.ChineseMonth2Number(month) + "-" + DateUtilsEX.ChineseDay2Number(day) + " 00:00:00";
		}
		element.addElement("fzrq").addText(fzrq);//发证日期
		if(StringUtils.isNotBlank(zsbh)){
			element.addElement("fzjg").addText((String)map.get("fzjg")==null?"广州市城市管理委员会":(String)map.get("fzjg"));//发证机关
		}else{
			element.addElement("fzjg").addText("");//发证机关
		}
		
		element.addElement("xljy_ssys_sl").addText((String)map.get("xljy_ssys_sl")==null?"":(String)map.get("xljy_ssys_sl"));
		element.addElement("xljy_ssys_ms").addText("");
		element.addElement("xljy_bkys_sl").addText((String)map.get("xljy_bkys_sl")==null?"":(String)map.get("xljy_bkys_sl"));
		element.addElement("xljy_bkys_ms").addText("");
		element.addElement("xljy_zkys_sl").addText((String)map.get("xljy_zkys_sl")==null?"":(String)map.get("xljy_zkys_sl"));
		element.addElement("xljy_zkys_ms").addText("");
		element.addElement("xljy_hj_sl").addText((String)map.get("xljy_hj_sl")==null?"":(String)map.get("xljy_hj_sl"));
		element.addElement("xljy_hj_ms").addText("");
		
		element.addElement("zyjsry_gj_sl").addText((String)map.get("zyjsry_gj_sl")==null?"":(String)map.get("zyjsry_gj_sl"));
		element.addElement("zyjsry_gj_ms").addText("");
		element.addElement("zyjsry_zj_sl").addText((String)map.get("zyjsry_zj_sl")==null?"":(String)map.get("zyjsry_zj_sl"));
		element.addElement("zyjsry_zj_ms").addText("");
		element.addElement("zyjsry_cj_sl").addText((String)map.get("zyjsry_cj_sl")==null?"":(String)map.get("zyjsry_cj_sl"));
		element.addElement("zyjsry_cj_ms").addText("");
		element.addElement("zyjsry_hj_sl").addText((String)map.get("zyjsry_hj_sl")==null?"":(String)map.get("zyjsry_hj_sl"));
		element.addElement("zyjsry_hj_ms").addText("");
		
		element.addElement("aqglry_zcaqgcs_sl").addText((String)map.get("aqglry_zcaqgcs_sl")==null?"":(String)map.get("aqglry_zcaqgcs_sl"));
		element.addElement("aqglry_zcaqgcs_ms").addText("");
		element.addElement("aqglry_zcgjaqzr_sl").addText((String)map.get("aqglry_zcgjaqzr_sl")==null?"":(String)map.get("aqglry_zcgjaqzr_sl"));
		element.addElement("aqglry_zcgjaqzr_ms").addText("");
		element.addElement("aqglry_zczjaqzr_sl").addText((String)map.get("aqglry_zczjaqzr_sl")==null?"":(String)map.get("aqglry_zczjaqzr_sl"));
		element.addElement("aqglry_zczjaqzr_ms").addText("");
		element.addElement("aqglry_zccjaqzr_sl").addText((String)map.get("aqglry_zccjaqzr_sl")==null?"":(String)map.get("aqglry_zccjaqzr_sl"));
		element.addElement("aqglry_zccjaqzr_ms").addText("");
		
		element.addElement("cyryzs").addText((String)map.get("cyryzs")==null?"":(String)map.get("cyryzs"));
		
		//以下的是系统里面没有的字段
		element.addElement("comCode").addText("");
		element.addElement("region").addText("");
		element.addElement("abs_x").addText("");
		element.addElement("abs_y").addText("");
		element.addElement("area").addText("");
		element.addElement("community").addText("");
		element.addElement("street").addText("");
		element.addElement("district").addText("");
		element.addElement("fddbr_dh").addText("");
		element.addElement("response_phone").addText("");
		element.addElement("zyfzr_dh").addText("");
		element.addElement("aqfzr_dh").addText("");
		element.addElement("jsfzr_dh").addText("");
		element.addElement("dispatchPhone").addText("");
		element.addElement("service_man_total").addText("");
		element.addElement("safe_man_num").addText("");
		element.addElement("safe_man_des").addText("");
		
	
	}
	
	/**
	 * 生成经营许可企业供应站信息的xml
	 * @param map
	 * @param element
	 */
	private void creatGyzXml(Map map,Element element){
		element.addElement("aoge_id").addText((String)map.get("sqdw"));
		element.addElement("zdmc").addText((String)map.get("gyzmc")==null?"":(String)map.get("gyzmc"));//站点名称

		element.addElement("sslb").addText((String)map.get("sslb")==null?"":(String)map.get("sslb"));//设施类别
		element.addElement("zdmj").addText((String)map.get("zdmj")==null?"":StringUtilsEX.getNumFromStr((String)map.get("zdmj")));//占地面积
//		element.addElement("qyid").addText((String)map.get("qyid")==null?"":(String)map.get("qyid"));//所属企业ID
		element.addElement("lxdh").addText((String)map.get("lxdh")==null?"":(String)map.get("lxdh"));//负责人电话
		element.addElement("zddz").addText((String)map.get("gyzdz")==null?"":(String)map.get("gyzdz"));//详细地址
		element.addElement("service_phone").addText((String)map.get("lxdh")==null?"":(String)map.get("lxdh"));//服务电话
		element.addElement("zyfzr").addText((String)map.get("zyfzr")==null?"":(String)map.get("zyfzr"));//公司主要负责人
		element.addElement("aqfzr").addText((String)map.get("aqfzr")==null?"":(String)map.get("aqfzr"));//公司安全负责人
		element.addElement("cscqr").addText((String)map.get("cscqr")==null?"":(String)map.get("cscqr"));//场地产权人
		element.addElement("comp_date").addText("");//竣工日期
		if((String)map.get("bszsbh") != null){
			element.addElement("zjmc").addText("许可标识");//证件名称
			element.addElement("fzjg").addText((String)map.get("fzorg")==null?"广州市城市管理委员会":(String)map.get("fzorg"));//颁发机关
		}else{
			element.addElement("zjmc").addText("");//证件名称
			element.addElement("fzjg").addText("");//颁发机关
		}
		String fzdate = "";
		if(StringUtils.isNotBlank((String)map.get("fzdate"))){
			fzdate = ((String)map.get("fzdate")).substring(0, ((String)map.get("fzdate")).length()) + " 00:00:00";
		}
		element.addElement("fzrq").addText(fzdate);//颁发日期
		element.addElement("zsbh").addText((String)map.get("bszsbh")==null?"":(String)map.get("bszsbh"));//证件编号
		String validdate = "";
		if(StringUtils.isNotBlank((String)map.get("validdate"))){
			validdate = ((String)map.get("validdate")).substring(0, ((String)map.get("validdate")).length()) + " 00:00:00";
		}
		element.addElement("yxq").addText(validdate);//有效期
		
		
		//以下是没有的字段
		element.addElement("siteCode").addText("");//站点编号
		element.addElement("community").addText("");//社区
		element.addElement("street").addText("");//街道
		element.addElement("district").addText("");//行政区
		element.addElement("abs_x").addText("");//绝对位置x
		element.addElement("abs_y").addText("");//绝对位置y
		element.addElement("descript").addText("");//描述
		element.addElement("region").addText("");//所属区域
		element.addElement("period_date").addText("");//安全评价报告有效期
	}
	
	/**
	 * 生成经营许可企业燃气接收站、储配站、汽车加气站信息的xml
	 * @param map
	 * @param element
	 */
	private void creatJszXml(Map map,Element element){
		element.addElement("aoge_id").addText((String)map.get("sqdw"));
//		element.addElement("qyid").addText((String)map.get("qyid")==null?"":(String)map.get("qyid"));//所属企业ID
		
		element.addElement("sslb").addText((String)map.get("sslb")==null?"":(String)map.get("sslb"));//设施类别
		element.addElement("zdmc").addText((String)map.get("ssmc")==null?"":(String)map.get("ssmc"));//站点名称
		element.addElement("zdmj").addText((String)map.get("zdmj")==null?"":StringUtilsEX.getNumFromStr((String)map.get("zdmj")));//占地面积
		element.addElement("lxdh").addText((String)map.get("lxdh")==null?"":(String)map.get("lxdh"));//负责人电话
		element.addElement("zddz").addText((String)map.get("ssdz")==null?"":(String)map.get("ssdz"));//详细地址
		element.addElement("service_phone").addText((String)map.get("lxdh")==null?"":(String)map.get("lxdh"));//服务电话
		element.addElement("zyfzr").addText((String)map.get("zyfzr")==null?"":(String)map.get("zyfzr"));//公司主要负责人
		element.addElement("aqfzr").addText((String)map.get("aqfzr")==null?"":(String)map.get("aqfzr"));//公司安全负责人
		
		String jgysrq = "";
		if(StringUtils.isNotBlank((String)map.get("jgysrq"))){
			Date date = DateUtilsEX.getStringToDate((String)map.get("jgysrq"));
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			jgysrq = formatter.format(date);
//			jgysrq = ((String)map.get("jgysrq")).replace("年", "-").replace("月", "-").replace("日", "") + " 00:00:00";
		}
		element.addElement("comp_date").addText(jgysrq);//竣工日期
		
		element.addElement("azxs").addText((String)map.get("azxs")==null?"":(String)map.get("azxs"));//安装形式
		element.addElement("cgsl").addText((String)map.get("cgsl")==null?"":StringUtilsEX.getStringNum((String)map.get("cgsl")));//储罐数量
		element.addElement("cgzrj").addText((String)map.get("cgzrj")==null?"":StringUtilsEX.getStringNum((String)map.get("cgzrj")));//储罐总容积
		element.addElement("dgzdrj").addText((String)map.get("dgzdrj")==null?"":StringUtilsEX.getStringNum((String)map.get("dgzdrj")));//单罐最大容积
		
		
		if((String)map.get("bszsbh") != null){
			element.addElement("zjmc").addText("许可标识");//证件名称
			element.addElement("fzjg").addText((String)map.get("fzorg")==null?"广州市城市管理委员会":(String)map.get("fzorg"));//颁发机关
		}else{
			element.addElement("zjmc").addText("");//证件名称
			element.addElement("fzjg").addText("");//颁发机关
		}
		String fzdate = "";
		if(StringUtils.isNotBlank((String)map.get("fzdate"))){
			fzdate = ((String)map.get("fzdate")).substring(0, ((String)map.get("fzdate")).length()) + " 00:00:00";
		}
		element.addElement("fzrq").addText(fzdate);//颁发日期
		element.addElement("zsbh").addText((String)map.get("bszsbh")==null?"":(String)map.get("bszsbh"));//证件编号
		String validdate = "";
		if(StringUtils.isNotBlank((String)map.get("validdate"))){
			validdate = ((String)map.get("validdate")).substring(0, ((String)map.get("validdate")).length()) + " 00:00:00";
		}
		element.addElement("yxq").addText(validdate);//有效期
		
		
		//以下是没有的字段
		element.addElement("cscqr").addText("");//场地产权人
		element.addElement("siteCode").addText("");//站点编号
		element.addElement("community").addText("");//社区
		element.addElement("street").addText("");//街道
		element.addElement("district").addText("");//行政区
		element.addElement("abs_x").addText("");//绝对位置x
		element.addElement("abs_y").addText("");//绝对位置y
		element.addElement("descript").addText("");//描述
		element.addElement("region").addText("");//所属区域
		element.addElement("period_date").addText("");//安全评价报告有效期
	}
	
	/**
	 * 生成抢险点的xml
	 * @param map
	 * @param element
	 */
	private void creatQxdXml(Map map,Element element){
		element.addElement("aoge_id").addText((String)map.get("sqdw"));
		
		element.addElement("qxdrs").addText((String)map.get("qxdrs")==null?"":StringUtilsEX.getNumFromStr((String)map.get("qxdrs")));//燃气抢险员数量
		element.addElement("qxcl").addText((String)map.get("qxcl")==null?"":StringUtilsEX.getNumFromStr((String)map.get("qxcl")));//燃气抢险车数量
		element.addElement("mc").addText((String)map.get("mc")==null?"":(String)map.get("mc"));//抢险点名称
		element.addElement("dz").addText((String)map.get("dz")==null?"":(String)map.get("dz"));//抢险点地址
		element.addElement("dh").addText((String)map.get("dh")==null?"":(String)map.get("dh"));//抢险点电话
		element.addElement("fzr").addText((String)map.get("fzr")==null?"":(String)map.get("fzr"));//负责人
		element.addElement("fzr_dh").addText((String)map.get("fzr_dh")==null?"":(String)map.get("fzr_dh"));//负责人电话
		
		String qyid = (String)map.get("qyid")==null?"":(String)map.get("qyid");
		String acceptNo = (String)map.get("accept_no")==null?"":(String)map.get("accept_no");
		if(StringUtils.isNotBlank(qyid) && StringUtils.isNotBlank(acceptNo)){
			List qxsbList = this.getQxsb(acceptNo, qyid);
			if(qxsbList != null && qxsbList.size()>0){
				Element qxsbs = element.addElement("qxsbs");
				for(int i=0 ;i<qxsbList.size();i++){
					Map qxsbMap = (Map) qxsbList.get(i);
					Element qxsb = qxsbs.addElement("qxsb");
					qxsb.addElement("mc").addText((String)qxsbMap.get("mc")==null?"":(String)qxsbMap.get("mc"));//抢险点名称
					qxsb.addElement("ggxh").addText((String)qxsbMap.get("ggxh")==null?"":(String)qxsbMap.get("ggxh"));//抢险点地址
					qxsb.addElement("sl").addText((String)qxsbMap.get("sl")==null?"":StringUtilsEX.getNumFromStr((String)qxsbMap.get("sl")));//抢险点电话
				}
			}
		}
	}
	
	private List getApplyContent(String procinstId) {
		String sql="  select * from jbpm4_hist_task t where t.procinst_='"+procinstId+"' order by t.dbid_ asc";
		List list=this.ExChangeDataDao.queryForList(sql);
		return list;
	}
	
	/**
	 * 经营许可的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getJyxk(String pageNo, String pageSize,String sqdw,String ywId,String qyid){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getJyxkSql(sqdw, ywId, qyid);
		return this.ExChangeDataDao.searchByPage(sql, page, null);
	}
	
	/**
	 * 经营许可的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getRqQyzz(String pageNo, String pageSize,String sqdw,String ywId,String qyid){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getRqQyzzSql( sqdw, ywId, qyid);
		return this.ExChangeDataDao.searchByPage(sql, page, null);
	}
	
	/**
	 * 抢险点的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getQxd(String pageNo, String pageSize,String sqdw,String acceptNo,String qyid){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getQxdSql( qyid, acceptNo);
		return this.ExChangeDataDao.searchByPage(sql, page, null);
	}
	
	/**
	 * 经营许可供应站的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getGyz(String pageNo, String pageSize){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getGyzSql();
		return this.ExChangeDataDao.searchByPage(sql, page, null);
	}
	/**
	 * 获取企业资质企业信息的sql
	 * @return
	 */
	private String getRqQyzzSql(String sqdw,String ywId,String qyid){
		StringBuffer sql=new StringBuffer();
		sql.append("select tq.*,qyxx.address,qyxx.post,qyxx.gszcdjh,qyxx.ywlxr,qyxx.ywlxrtel,qyxx.zczj,qyxx.email,qyxx.template_code,");
		sql.append("qyxx.zzdj,qyxx.zsbh,qyxx.year,qyxx.month,qyxx.day ");
		sql.append(" from YW_RQ_QYZZ_INFO tq,yw_com_qyxx qyxx");
		if(StringUtils.isNotBlank(ywId) && StringUtils.isNotBlank(qyid)){
			sql.append(" where  qyxx.id = tq.qyid and tq.qyid='"+qyid+"' and tq.id='"+ywId+"'");
		}else{
			sql.append(" ,(select distinct max(aa.id) as id, aa.sqdw, cc.template_code, cc.qyid from YW_RQ_QYZZ_INFO aa, WF_BUS_INSTANCE ins2,");
			sql.append(" (select distinct max(t.id) as qyid, t.sqdw, t.template_code ");
			sql.append(" from YW_COM_QYXX t");
			sql.append(" where t.template_code = 'rqrsqjazwxqyzz' and t.sqdw in ");
			sql.append(" (select distinct tt.sqdw from YW_RQ_QYZZ_INFO tt,WF_BUS_INSTANCE ins,YW_COM_QYXX qyxx where tt.accept_no = ins.bus_memo3 and qyxx.id = tt.qyid)");
			sql.append(" group by t.sqdw, t.template_code) cc");
			sql.append(" where aa.qyid = cc.qyid and aa.sqdw = cc.sqdw and aa.accept_no = ins2.bus_memo3 group by aa.sqdw, cc.template_code, cc.qyid) gg");
			
			sql.append(" where qyxx.id = tq.qyid and gg.qyid = tq.qyid ");
			sql.append("  and gg.sqdw = tq.sqdw and gg.id = tq.id");
		}		
		
		System.out.println("sql ============ "+sql);
		return sql.toString();
	}
	
	/**
	 * 获取经营许可企业信息的sql
	 * @return
	 */
	private String getJyxkSql(String sqdw,String ywId,String qyid){
		StringBuffer sql=new StringBuffer();
		sql.append("select distinct tq.*,qyxx.address,qyxx.post,qyxx.gszcdjh,qyxx.ywlxr,qyxx.ywlxrtel,qyxx.zczj,qyxx.email,qyxx.template_code,");
		sql.append(" cyryzkdjb.xljy_ssys_sl,cyryzkdjb.xljy_bkys_sl,cyryzkdjb.xljy_zkys_sl,cyryzkdjb.xljy_hj_sl, ");
		sql.append(" cyryzkdjb.zyjsry_gj_sl,cyryzkdjb.zyjsry_zj_sl,cyryzkdjb.zyjsry_cj_sl,cyryzkdjb.zyjsry_hj_sl,");
		sql.append(" cyryzkdjb.aqglry_zcaqgcs_sl,cyryzkdjb.aqglry_zcgjaqzr_sl,cyryzkdjb.aqglry_zczjaqzr_sl,cyryzkdjb.aqglry_zccjaqzr_sl,");
		sql.append(" cyryzkdjb.cyryzs");
		sql.append(" from yw_rq_qyjyxk tq, yw_com_qyxx qyxx, YW_RQ_QYJYXK_CYRYZKDJB cyryzkdjb");	
		if(StringUtils.isNotBlank(ywId) && StringUtils.isNotBlank(qyid)){
			sql.append(" where  qyxx.id = tq.qyid and cyryzkdjb.qyid = tq.qyid and tq.qyid='"+qyid+"' and tq.id='"+ywId+"'");
		}else{
			sql.append(" ,(select distinct max(aa.id) as id,aa.sqdw,cc.template_code,cc.qyid from yw_rq_qyjyxk aa,WF_BUS_INSTANCE ins2,");           
			sql.append(" (select distinct max(t.id) as qyid,t.sqdw, t.template_code ");
			sql.append(" from YW_COM_QYXX t ");
			sql.append(" where t.template_code in ('rqjyxkz_pzqy', 'rqjyxkz_gdqy') ");
			sql.append(" and t.sqdw in (select distinct ee.sqdw");
			sql.append(" from (select distinct tt.sqdw,tt.id,qyxx.template_code");
            sql.append(" from yw_rq_qyjyxk    tt,WF_BUS_INSTANCE ins,YW_COM_QYXX     qyxx");
            sql.append(" where tt.accept_no = ins.bus_memo3 and qyxx.id = tt.qyid) ee");
            sql.append(" inner join YW_PRINT_DOC doc on doc.template_code = ee.template_code and doc.yw_id = ee.id and doc.file_code = 'zyxzxkjds') ");
            sql.append(" group by t.sqdw, t.template_code) cc");
            sql.append("");

			sql.append(" where aa.qyid=cc.qyid and aa.sqdw=cc.sqdw and aa.accept_no = ins2.bus_memo3");
			sql.append(" group by aa.sqdw,cc.template_code,cc.qyid) gg ");		
			
			sql.append(" where qyxx.id = tq.qyid and cyryzkdjb.qyid = tq.qyid");
			sql.append(" and gg.qyid = tq.qyid and gg.sqdw = tq.sqdw and gg.id = tq.id");
		}
		System.out.println("JyxkSql ============ "+sql);
		return sql.toString();
	}
	
	/**
	 * 获取经营许可企业供应站信息的sql
	 * @return
	 */
	private String getGyzSql(){		
		StringBuffer sql=new StringBuffer();
		sql.append("select distinct ssylb.bszsbh,ssylb.validdate,ssylb.fzdate,ssylb.fzorg,t.* ");
		sql.append(" from YW_RQ_QYJYXK_PZRQGYZDJB t, YW_RQ_QYJYXK jyxk, YW_RQ_RQSSYLB ssylb");
		sql.append(" where jyxk.id = ssylb.qyjyxkid and t.qyid = jyxk.qyid");
		sql.append(" and t.accept_no = jyxk.accept_no and t.gyzmc = ssylb.ssmc");
		sql.append("");
		sql.append("");
		System.out.println("GyzSql ============ "+sql);
		return sql.toString();
	}
	
	/**
	 * 生成xml文件
	 * @param document
	 * @param outFilePath
	 * @throws Exception
	 */
	public void createXml(Document document, String outFilePath) throws Exception{
		String fileName = "abc.xml";
		//设置文件编码
		OutputFormat xmlFormat = OutputFormat.createPrettyPrint();
		xmlFormat.setEncoding("UTF-8");
		XMLWriter  xmlWriter = new XMLWriter(new FileOutputStream(outFilePath), xmlFormat);
		xmlWriter.write(document);
		//关闭
		xmlWriter.close();
	}
	
	/**
	 * 查询企业，用于查询企业的供应站
	 * @return
	 */
	public List getRqJyxkQy(String sqdw){
		StringBuffer sql=new StringBuffer();
		sql.append("select tq.sqdw,tq.qyid ");
		sql.append(" from yw_rq_qyjyxk tq, yw_com_qyxx qyxx, YW_RQ_QYJYXK_CYRYZKDJB cyryzkdjb,");		
		
		sql.append(" (select distinct max(aa.id) as id,aa.sqdw,cc.template_code,cc.qyid from yw_rq_qyjyxk aa,WF_BUS_INSTANCE ins2,");           
		sql.append(" (select distinct max(t.id) as qyid,t.sqdw, t.template_code ");
		sql.append(" from YW_COM_QYXX t ");
		sql.append(" where t.template_code in ('rqjyxkz_pzqy', 'rqjyxkz_gdqy') ");
		sql.append(" and t.sqdw in (select distinct tt.sqdw");
		sql.append(" from yw_rq_qyjyxk tt, WF_BUS_INSTANCE ins,YW_COM_QYXX qyxx");
		sql.append(" where tt.accept_no = ins.bus_memo3 and qyxx.id=tt.qyid)  group by t.sqdw, t.template_code) cc");
		sql.append(" where aa.qyid=cc.qyid and aa.sqdw=cc.sqdw and aa.accept_no = ins2.bus_memo3");
		sql.append(" group by aa.sqdw,cc.template_code,cc.qyid) gg ");		
		
		sql.append(" where qyxx.id = tq.qyid and cyryzkdjb.qyid = tq.qyid");
		sql.append(" and gg.qyid = tq.qyid and gg.sqdw = tq.sqdw and gg.id = tq.id");
		return ExChangeDataDao.queryForList(sql.toString());
	}
	
	/**
	 * 根据企业名称获取供应站
	 * @param sqdw
	 * @return
	 */
	public List getGyzBySqdw(String sqdw,String ywId,String qyid){
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct ssylb.bszsbh, ssylb.validdate, ssylb.fzdate, ssylb.fzorg,ssylb.sslb,");
		sql.append("t.gyzmc,t.zdmj,t.lxdh,t.gyzdz,t.zyfzr,t.aqfzr,t.cscqr");
		if(StringUtils.isNotBlank(ywId) && StringUtils.isNotBlank(qyid)){
			sql.append(",jyxk.sqdw ");
		}
		sql.append(" from YW_RQ_QYJYXK_PZRQGYZDJB t,  YW_RQ_RQSSYLB ssylb");
		if(StringUtils.isNotBlank(ywId) && StringUtils.isNotBlank(qyid)){
			sql.append(",YW_RQ_QYJYXK jyxk where t.qyid='"+qyid+"' and ssylb.qyjyxkid='"+ywId+"' and t.accept_no=jyxk.accept_no and ssylb.ssmc=t.gyzmc");
		}else{
			sql.append(" where ssylb.qyjyxkid in (select distinct t.id from yw_rq_qyjyxk t inner join YW_PRINT_DOC doc ");
			sql.append(" on doc.template_code in ('rqjyxkz_pzqy', 'rqjyxkz_gdqy') and doc.yw_id = t.id ");
			sql.append(" and doc.file_code = 'zyxzxkjds' and t.sqdw = '"+sqdw+"')");
			sql.append(" and t.qyid in (select distinct t.id from yw_com_qyxx t where t.sqdw = '"+sqdw+"' and t.template_code in ('rqjyxkz_pzqy', 'rqjyxkz_gdqy'))");
			sql.append(" and t.gyzmc = ssylb.ssmc and ssylb.zdlb='合格'");
		}
		
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		System.out.println("GyzSql2 ============ "+sql);
		return ExChangeDataDao.queryForList(sql.toString());
	}
	
	/**
	 * 根据企业名称获取燃气接收站、储配站、汽车加气站
	 * @param sqdw
	 * @return
	 */
	public List getJszBySqdw(String sqdw,String ywId,String qyid){
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct ssylb.bszsbh, ssylb.validdate, ssylb.fzdate, ssylb.fzorg,ssylb.sslb,");
		sql.append("tt.ssmc,tt.zdmj,tt.lxdh,tt.ssdz,tt.zyfzr,tt.aqrzr,tt.jgysrq,tt.cgsl,tt.azxs,tt.cgzrj,tt.dgzdrj");
		if(StringUtils.isNotBlank(ywId) && StringUtils.isNotBlank(qyid)){
			sql.append(",jyxk.sqdw ");
		}
		sql.append(" from YW_RQ_QYJYXK_RQJSZDJB tt, YW_RQ_RQSSYLB ssylb");
		
		if(StringUtils.isNotBlank(ywId) && StringUtils.isNotBlank(qyid)){
			sql.append(" , YW_RQ_QYJYXK jyxk where tt.qyid='"+qyid+"' and ssylb.qyjyxkid='"+ywId+"' and tt.accept_no=jyxk.accept_no and ssylb.ssmc=tt.ssmc");
		}else{
//			sql.append(" where ssylb.qyjyxkid in (select distinct t.id from yw_rq_qyjyxk t where t.sqdw = '"+sqdw+"')");
			sql.append(" where ssylb.qyjyxkid in (select distinct t.id from yw_rq_qyjyxk t inner join YW_PRINT_DOC doc ");
			sql.append(" on doc.template_code in ('rqjyxkz_pzqy', 'rqjyxkz_gdqy') and doc.yw_id = t.id ");
			sql.append(" and doc.file_code = 'zyxzxkjds' and t.sqdw = '"+sqdw+"')");
			sql.append(" and tt.qyid in (select distinct t.id from yw_com_qyxx t where t.sqdw = '"+sqdw+"' and t.template_code in ('rqjyxkz_pzqy', 'rqjyxkz_gdqy'))");
			sql.append(" and tt.ssmc = ssylb.ssmc and ssylb.zdlb='合格'");
		}		
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		System.out.println("jszSql ============ "+sql);
		return ExChangeDataDao.queryForList(sql.toString());
	}
	
	/**
	 * 获取抢险点
	 * @return
	 */
	public String getQxdSql(String qyid,String acceptNo){
		StringBuffer sql = new StringBuffer();
		
		
		sql.append("select distinct er.mc,er.dz,er.dh,er.sqdw,er.accept_no,yy.qxdrs,yy.qxcl,yy.qyid,yy.fzr,yy.dh fzr_dh from (select distinct tq.*,aa.sqdw");
		sql.append(" from YW_RQ_QYJYXK_RQQXDYLB tq, (");
		sql.append(" select distinct t.accept_no,t.sqdw,t.qyid from YW_RQ_QYJYXK t inner join YW_PRINT_DOC doc");
		sql.append(" on doc.template_code in ('rqjyxkz_pzqy', 'rqjyxkz_gdqy') and doc.yw_id = t.id and doc.file_code = 'zyxzxkjds' and t.sqdw in (");
		sql.append(" select distinct ee.sqdw from (select distinct tt.sqdw, tt.id, qyxx.template_code");
		sql.append(" from yw_rq_qyjyxk    tt, WF_BUS_INSTANCE ins, YW_COM_QYXX     qyxx where tt.accept_no = ins.bus_memo3 and qyxx.id = tt.qyid) ee");
		sql.append(" inner join YW_PRINT_DOC doc on doc.template_code = ee.template_code and doc.yw_id = ee.id and doc.file_code = 'zyxzxkjds')) aa");
		sql.append(" where tq.accept_no=aa.accept_no and tq.qyid=aa.qyid");
		if(StringUtils.isNotBlank(qyid) && StringUtils.isNotBlank(acceptNo)){
			sql.append(" and tq.qyid='"+qyid+"' and tq.accept_no='"+acceptNo+"' ");
		}
		sql.append(" )er left join (select distinct tp.* from YW_RQ_QYJYXK_RQQXSSDJB tp,");
		sql.append(" (select distinct t.accept_no,t.sqdw,t.qyid from YW_RQ_QYJYXK t inner join YW_PRINT_DOC doc");
		sql.append(" on doc.template_code in ('rqjyxkz_pzqy', 'rqjyxkz_gdqy') and doc.yw_id = t.id and doc.file_code = 'zyxzxkjds'");
		sql.append(" and t.sqdw in (select distinct ee.sqdw from (select distinct tt.sqdw, tt.id, qyxx.template_code");
		sql.append(" from yw_rq_qyjyxk    tt, WF_BUS_INSTANCE ins, YW_COM_QYXX     qyxx where tt.accept_no = ins.bus_memo3 and qyxx.id = tt.qyid) ee");
		sql.append(" inner join YW_PRINT_DOC doc on doc.template_code = ee.template_code and doc.yw_id = ee.id and doc.file_code = 'zyxzxkjds')) aa");
		sql.append(" where tp.accept_no=aa.accept_no and tp.qyid=aa.qyid");
		if(StringUtils.isNotBlank(qyid) && StringUtils.isNotBlank(acceptNo)){
			sql.append(" and tp.qyid='"+qyid+"' and tp.accept_no='"+acceptNo+"' ");
		}
		sql.append(" )yy");
		sql.append(" on er.accept_no=yy.accept_no and er.qyid=yy.qyid order by er.sqdw");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		System.out.println("所有的Qxd ============ "+sql);
		return sql.toString();
	}
	
	/**
	 * 获取抢险设备
	 * @param sqdw
	 * @return
	 */
	public List getQxsb(String acceptNo,String qyid){
		StringBuffer sql = new StringBuffer();
				
		sql.append("select distinct tq.mc,tq.ggxh,tq.sl,tq.qyid,aa.sqdw from YW_RQ_QYJYXK_RQQXSBYLB tq,");
		sql.append(" (select distinct t.accept_no,t.sqdw,t.qyid from YW_RQ_QYJYXK t inner join YW_PRINT_DOC doc");
		sql.append(" on doc.template_code in ('rqjyxkz_pzqy', 'rqjyxkz_gdqy') and doc.yw_id = t.id and doc.file_code = 'zyxzxkjds' and t.sqdw in ");
		sql.append(" (select distinct ee.sqdw from (select distinct tt.sqdw,  tt.id, qyxx.template_code");
		sql.append(" from yw_rq_qyjyxk    tt, WF_BUS_INSTANCE ins, YW_COM_QYXX     qyxx");
		sql.append(" where tt.accept_no = ins.bus_memo3 and qyxx.id = tt.qyid) ee inner join YW_PRINT_DOC doc");
		sql.append(" on doc.template_code = ee.template_code and doc.yw_id = ee.id and doc.file_code = 'zyxzxkjds')) aa");
		sql.append(" where tq.accept_no=aa.accept_no and tq.qyid=aa.qyid and tq.qyid='"+qyid+"' and tq.accept_no='"+acceptNo+"'");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		sql.append("");
		System.out.println("抢险设备sql ============ "+sql);
		return ExChangeDataDao.queryForList(sql.toString());
	}
	
	/**
	 * 命名xml
	 * @param flag 判断是企业还是站点
	 * @param zdFlag 判断是供应站还是其他
	 * @param templateCode 
	 * @param ywId
	 * @param qyid
	 * @return
	 */
	public String createXMLName(String flag,String zdFlag,String templateCode,String ywId,String qyid){
		Date date = new Date();
		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss");
		String dateStr = sf.format(date);
		int random = new Random().nextInt(1000000);
		String fileName = "";		
		if(StringUtils.isNotBlank(zdFlag)){
			fileName = flag + "_" + dateStr + "_" + random + "_" + templateCode + "_" + ywId + "_" + qyid + "_" + zdFlag +".xml";
		}else{
			fileName = flag + "_" + dateStr + "_" + random + "_" + templateCode + "_" + ywId + "_" + qyid + ".xml";
		}
		return fileName;
	}
}
